﻿using Dapper;
using System;
using BCEntity.Trading.SaleGoods.SaleGoods;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;
using BCCommon;

namespace BCData.Trading.SaleGoods.SaleGoods
{
    /// <summary>
    /// 销售商品
    /// </summary>
    public class SaleGoodsData : ISaleGoodsData
    {
        private readonly IDatabaseContext databaseContext;
        private const string QUERY_EXISTS = @"SELECT 1 FROM salegoods";
        private const string QUERY = @"SELECT SaleGoodsId,TwoImage,ThreeImage,FourImage,FiveImage,HasBargain,HasStepPrice,SaleAmount,StepOneStartNumber,StepOneEndNumber,
                                            StepTwoStartNumber,SaleGoodsCategoryId,StepTwoEndNumber,StepThreeStartNumber,StepThreeEndNumber,StepOnePrice,StepTwoPrice,
                                            StepThreePrice,SaleUnit,HasContentHeaderVideo,ContentHeaderVideo,HasContentSummary,CategoryName,ContentSummary,Content,
                                            HasRelatedProducts,RelatedProductsIdPath,InventoryCount,SaleCount,Enabled,CreateAdminId,CreateAdminName,CreateTime,
                                            CategoryNamePinyin,UpdateTime,EditAdminId,EditAdminName,GoodsName,GoodsNamePinyin,SKU,Keyword,OneImage 
                                        FROM salegoods";
        public SaleGoodsData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }
        #region 检查销售商品是否存在
        public async Task<bool> Exists(long saleGoodsId)
        {
            string sql = string.Format(@"{0} WHERE SaleGoodsId=?SaleGoodsId", QUERY_EXISTS);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SaleGoodsId", saleGoodsId, DbType.Int64);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }
        #endregion

        #region 是否存在此分类的商品记录
        public async Task<bool> CategoryExists(long saleGoodsCategoryId)
        {
            string sql = string.Format(@"{0} WHERE SaleGoodsCategoryId=?SaleGoodsCategoryId LIMIT 1", QUERY_EXISTS);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SaleGoodsCategoryId", saleGoodsCategoryId, DbType.Int64);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }
        #endregion

        #region 是否存在此商品SKU记录
        public async Task<bool> SKUExists(string sku)
        {
            string sql = string.Format(@"{0} WHERE SKU=?SKU LIMIT 1", QUERY_EXISTS);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SKU", sku, DbType.AnsiString, size: 50);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }
        #endregion        
        #region 添加销售商品
        public async Task<SaleGoodsEntity> Add(SaleGoodsEntity entity)
        {
            string sql = SqlCommands.GetInsertSql("salegoods", new string[] { "SaleGoodsCategoryId","CategoryName","CategoryNamePinyin","GoodsName", "GoodsNamePinyin",
                "SKU","Keyword", "OneImage","TwoImage", "ThreeImage", "FourImage", "FiveImage", "HasBargain", "HasStepPrice","SaleAmount", "SaleUnit",
                "StepOneStartNumber", "StepOneEndNumber", "StepTwoStartNumber", "StepTwoEndNumber", "StepThreeStartNumber", "StepThreeEndNumber",
                "StepOnePrice", "StepTwoPrice", "StepThreePrice", "HasContentHeaderVideo", "ContentHeaderVideo", "HasContentSummary", "ContentSummary", "Content", 
                "HasRelatedProducts", "RelatedProductsIdPath", "InventoryCount","CreateAdminId", "CreateAdminName"  });
            sql = string.Format("{0} {1} WHERE SaleGoodsId=LAST_INSERT_ID();", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?HasBargain", entity.HasBargain, DbType.Boolean);
            parameters.Add("?HasStepPrice", entity.HasStepPrice, DbType.Boolean);
            parameters.Add("?SaleGoodsCategoryId", entity.SaleGoodsCategoryId, DbType.Int64, size: 20);
            parameters.Add("?SaleUnit", entity.SaleUnit?.Trim(), DbType.AnsiString, size: 10);
            parameters.Add("?HasContentHeaderVideo", entity.HasContentHeaderVideo, DbType.Boolean);
            parameters.Add("?ContentHeaderVideo", entity.ContentHeaderVideo?.Trim(), DbType.AnsiString, size: 1000);
            parameters.Add("?HasContentSummary", entity.HasContentSummary, DbType.Boolean);
            parameters.Add("?ContentSummary", entity.ContentSummary?.Trim(), DbType.String);
            parameters.Add("?CategoryName", entity.CategoryName?.Trim(), DbType.AnsiString, size: 10);
            parameters.Add("?Content", entity.Content, DbType.String);
            parameters.Add("?HasRelatedProducts", entity.HasRelatedProducts, DbType.Boolean);
            parameters.Add("?InventoryCount", entity.InventoryCount, DbType.Int32, size: 11);
            parameters.Add("?CreateAdminId", entity.CreateAdminId, DbType.Int64, size: 20);
            parameters.Add("?CreateAdminName", entity.CreateAdminName?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?CategoryNamePinyin", entity.CategoryNamePinyin?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?RelatedProductsIdPath", entity.RelatedProductsIdPath?.Trim(), DbType.AnsiString, size: 200);
            parameters.Add("?GoodsName", entity.GoodsName?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?GoodsNamePinyin", entity.GoodsNamePinyin?.Trim(), DbType.AnsiString, size: 400);
            parameters.Add("?SKU", entity.Sku?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?Keyword", entity.Keyword?.Trim(), DbType.AnsiString, size: 200);
            parameters.Add("?OneImage", entity.OneImage?.Trim(), DbType.AnsiString, size: 1000);
            parameters.Add("?TwoImage", entity.TwoImage?.Trim(), DbType.AnsiString, size: 1000);
            parameters.Add("?ThreeImage", entity.ThreeImage?.Trim(), DbType.AnsiString, size: 1000);
            parameters.Add("?FourImage", entity.FourImage?.Trim(), DbType.AnsiString, size: 1000);
            parameters.Add("?FiveImage", entity.FiveImage?.Trim(), DbType.AnsiString, size: 1000);
            parameters.Add("?SaleAmount", entity.SaleAmount, DbType.Decimal, size: 16);
            parameters.Add("?StepOneStartNumber", entity.StepOneStartNumber, DbType.Int32, size: 11);
            parameters.Add("?StepOneEndNumber", entity.StepOneEndNumber, DbType.Int32, size: 11);
            parameters.Add("?StepTwoStartNumber", entity.StepTwoStartNumber, DbType.Int32, size: 11);
            parameters.Add("?StepTwoEndNumber", entity.StepTwoEndNumber, DbType.Int32, size: 11);
            parameters.Add("?StepThreeStartNumber", entity.StepThreeStartNumber, DbType.Int32, size: 11);
            parameters.Add("?StepThreeEndNumber", entity.StepThreeEndNumber, DbType.Int32, size: 11);
            parameters.Add("?StepOnePrice", entity.StepOnePrice, DbType.Decimal, size: 16);
            parameters.Add("?StepTwoPrice", entity.StepTwoPrice, DbType.Decimal, size: 16);
            parameters.Add("?StepThreePrice", entity.StepThreePrice, DbType.Decimal, size: 16);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<SaleGoodsEntity>(sql, parameters, databaseContext.Transaction);

        }
        #endregion

        #region 修改销售商品
        public async Task<SaleGoodsEntity> Update(SaleGoodsEntity entity)
        {
            string sql = SqlCommands.GetUpdateSql("salegoods", new string[] { "TwoImage", "ThreeImage", "FourImage", "FiveImage", "HasBargain", "HasStepPrice", "SaleAmount",
                "StepOneStartNumber", "StepOneEndNumber", "StepTwoStartNumber", "SaleGoodsCategoryId", "StepTwoEndNumber", "StepThreeStartNumber", "StepThreeEndNumber",
                "StepOnePrice", "StepTwoPrice", "StepThreePrice", "SaleUnit", "HasContentHeaderVideo", "ContentHeaderVideo", "HasContentSummary", "CategoryName",
                "ContentSummary", "Content", "HasRelatedProducts", "RelatedProductsIdPath", "InventoryCount", "SaleCount",
                "CategoryNamePinyin", "EditAdminId", "EditAdminName", "GoodsName", "GoodsNamePinyin", "SKU", "Keyword", "OneImage" }, new string[] { "SaleGoodsId" });
            sql = string.Format("{0} {1} WHERE SaleGoodsId=?SaleGoodsId;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SaleGoodsId", entity.SaleGoodsId, DbType.Int64, size: 20);
            parameters.Add("?TwoImage", entity.TwoImage?.Trim(), DbType.AnsiString, size: 1000);
            parameters.Add("?ThreeImage", entity.ThreeImage?.Trim(), DbType.AnsiString, size: 1000);
            parameters.Add("?FourImage", entity.FourImage?.Trim(), DbType.AnsiString, size: 1000);
            parameters.Add("?FiveImage", entity.FiveImage?.Trim(), DbType.AnsiString, size: 1000);
            parameters.Add("?HasBargain", entity.HasBargain, DbType.Boolean);
            parameters.Add("?HasStepPrice", entity.HasStepPrice, DbType.Boolean);
            parameters.Add("?SaleAmount", entity.SaleAmount, DbType.Decimal, size: 16);
            parameters.Add("?StepOneStartNumber", entity.StepOneStartNumber, DbType.Int32, size: 11);
            parameters.Add("?StepOneEndNumber", entity.StepOneEndNumber, DbType.Int32, size: 11);
            parameters.Add("?StepTwoStartNumber", entity.StepTwoStartNumber, DbType.Int32, size: 11);
            parameters.Add("?SaleGoodsCategoryId", entity.SaleGoodsCategoryId, DbType.Int64, size: 20);
            parameters.Add("?StepTwoEndNumber", entity.StepTwoEndNumber, DbType.Int32, size: 11);
            parameters.Add("?StepThreeStartNumber", entity.StepThreeStartNumber, DbType.Int32, size: 11);
            parameters.Add("?StepThreeEndNumber", entity.StepThreeEndNumber, DbType.Int32, size: 11);
            parameters.Add("?StepOnePrice", entity.StepOnePrice, DbType.Decimal, size: 16);
            parameters.Add("?StepTwoPrice", entity.StepTwoPrice, DbType.Decimal, size: 16);
            parameters.Add("?StepThreePrice", entity.StepThreePrice, DbType.Decimal, size: 16);
            parameters.Add("?SaleUnit", entity.SaleUnit?.Trim(), DbType.AnsiString, size: 10);
            parameters.Add("?HasContentHeaderVideo", entity.HasContentHeaderVideo, DbType.Boolean);
            parameters.Add("?ContentHeaderVideo", entity.ContentHeaderVideo?.Trim(), DbType.AnsiString, size: 1000);
            parameters.Add("?HasContentSummary", entity.HasContentSummary, DbType.Boolean);
            parameters.Add("?CategoryName", entity.CategoryName?.Trim(), DbType.AnsiString, size: 10);
            parameters.Add("?ContentSummary", entity.ContentSummary?.Trim(), DbType.String);
            parameters.Add("?Content", entity.Content, DbType.String);
            parameters.Add("?HasRelatedProducts", entity.HasRelatedProducts, DbType.Boolean);
            parameters.Add("?RelatedProductsIdPath", entity.RelatedProductsIdPath?.Trim(), DbType.AnsiString, size: 200);
            parameters.Add("?InventoryCount", entity.InventoryCount, DbType.Int32, size: 11);
            parameters.Add("?SaleCount", entity.SaleCount, DbType.Int32, size: 11);
            parameters.Add("?CategoryNamePinyin", entity.CategoryNamePinyin?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?EditAdminId", entity.EditAdminId, DbType.Int64, size: 20);
            parameters.Add("?EditAdminName", entity.EditAdminName?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?GoodsName", entity.GoodsName?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?GoodsNamePinyin", entity.GoodsNamePinyin?.Trim(), DbType.AnsiString, size: 400);
            parameters.Add("?SKU", entity.Sku?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?Keyword", entity.Keyword?.Trim(), DbType.AnsiString, size: 200);
            parameters.Add("?OneImage", entity.OneImage?.Trim(), DbType.AnsiString, size: 1000);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<SaleGoodsEntity>(sql, parameters, databaseContext.Transaction);
        }
        #endregion

        #region 删除销售商品
        public async Task<bool> Delete(long saleGoodsId)
        {
            string sql = @"DELETE FROM salegoods WHERE SaleGoodsId=?SaleGoodsId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SaleGoodsId", saleGoodsId, DbType.Int64);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }
        #endregion

        #region 获取销售商品
        public async Task<SaleGoodsEntity> GetEntity(long saleGoodsId)
        {
            string sql = string.Format(@"{0} WHERE SaleGoodsId=?SaleGoodsId", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SaleGoodsId", saleGoodsId, DbType.Int64);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<SaleGoodsEntity>(sql, parameters);
        }
        #endregion

        #region 获取销售商品
        public async Task<SaleGoodsEntity> GetEntity(string sku)
        {
            string sql = string.Format(@"{0} WHERE SKU=?SKU", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SKU", sku, DbType.AnsiString);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<SaleGoodsEntity>(sql, parameters);
        }
        #endregion

        #region 获取销售商品列表
        public async Task<IEnumerable<SaleGoodsEntity>> GetList()
        {
            string sql = string.Format(@"{0} WHERE Enabled=1", QUERY);
            return await this.databaseContext.Slave.QueryAsync<SaleGoodsEntity>(sql);
        }
        #endregion

        #region 获取销售商品分页
        public async Task<(IEnumerable<SaleGoodsEntity>, int)> Query(long? saleGoodsCategoryId, string categoryName, string goodsName, string sku, bool? enabled, DateTime? start, DateTime? end, string sortName, bool? ascending, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();

            if (enabled.HasValue)
            {
                wheres.Add("Enabled=?Enabled");
                parameters.Add("?Enabled", enabled.Value, DbType.Boolean);
            }
            if (saleGoodsCategoryId.HasValue)
            {
                wheres.Add("SaleGoodsCategoryId=?SaleGoodsCategoryId");
                parameters.Add("?SaleGoodsCategoryId", saleGoodsCategoryId.Value, DbType.Int64);
            }
            if (!string.IsNullOrWhiteSpace(categoryName))
            {
                wheres.Add("CategoryName LIKE CONCAT('%',CONCAT(?CategoryName,'%'))");
                parameters.Add("?CategoryName", categoryName?.Trim(), DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(goodsName))
            {
                wheres.Add("GoodsName LIKE CONCAT('%',CONCAT(?GoodsName,'%'))");
                parameters.Add("?GoodsName", goodsName?.Trim(), DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(sku))
            {
                wheres.Add("SKU LIKE CONCAT('%',CONCAT(?SKU,'%'))");
                parameters.Add("?SKU", sku?.Trim(), DbType.AnsiString);
            }
            if (start.HasValue && !end.HasValue)
            {
                wheres.Add("CreateTime>=?Start");
                parameters.Add("?Start", start.Value, DbType.DateTime);
            }
            else if (!start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime<=?End");
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            else if (start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime>=?Start AND CreateTime<=?End");
                parameters.Add("?Start", start.Value, DbType.DateTime);
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            string sql = string.Format(@"{0} {1} ORDER BY {4} {5} LIMIT {2},{3}; SELECT COUNT(1) FROM salegoods {1}",
                  QUERY,
                  wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                  (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                  pageSize,
                  string.IsNullOrWhiteSpace(sortName) ? "CreateTime" : sortName,
                  ascending.HasValue && ascending.Value ? "ASC" : "DESC");
            return await this.databaseContext.Slave.QueryMultipleAsync<SaleGoodsEntity, int>(sql, parameters);
        }
        #endregion

        #region 启用销售商品
        public async Task<bool> Enabled(long saleGoodsId)
        {
            string sql = @"UPDATE salegoods SET Enabled=1 WHERE SaleGoodsId=?SaleGoodsId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SaleGoodsId", saleGoodsId, DbType.Int64);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }
        #endregion

        #region 禁用销售商品
        public async Task<bool> Disabled(long saleGoodsId)
        {
            string sql = @"UPDATE salegoods SET Enabled=0 WHERE SaleGoodsId=?SaleGoodsId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SaleGoodsId", saleGoodsId, DbType.Int64);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }
        #endregion
    }
}



